package com.sduept.nwld.test;

import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;



public class GenLine extends DBConnection {

	private Map<String,List<Object[]>> lts_single = new HashMap<>();
	private Map<String,List<Object[]>> lts_loop = new HashMap<>();
	private Map<String,List<Object[]>> lts_dc = new HashMap<>();
	private Map<String,List<Object[]>> lts_notower = new HashMap<>();

	public GenLine query() throws Exception {
		String getlinesql_notower="select t.line_cim_id,t.loop_cim_id,t.linename,t.loopname,t.towerno,t.is_loop,t.isshow,t.voltage,t.longitude,t.latitude from t_ld_linetowerbylineid_500kv t where t.is_loop = 0 and t.isshow=3 order by t.linename,t.sel";
		String getlinesql_dc=" select t.line_cim_id,t.loop_cim_id,t.linename,t.loopname,t.towerno,t.is_loop,t.isshow,t.voltage,t.longitude,t.latitude from t_ld_linetowerbylineid_500kv t where t.is_loop = 0 and t.isshow=1 and t.linename like '%直流%' order by t.linename,t.sel";
		String getlinesql_single = "select t.line_cim_id,t.loop_cim_id,t.linename,t.loopname,t.towerno,t.is_loop,t.isshow,t.voltage,t.longitude,t.latitude from t_ld_linetowerbylineid_500kv t where t.is_loop = 0 and t.isshow=1 order by t.linename,t.sel";
		String getlinesql_loop = "select t.line_cim_id,t.loop_cim_id,t.linename,t.loopname,t.towerno,t.is_loop,t.isshow,t.voltage,t.longitude,t.latitude from t_ld_linetowerbylineid_500kv t where t.is_loop = 1 and t.isshow=1 order by t.linename,t.sel";
		java.sql.Statement st = null;
		getConnection();
		try {
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(getlinesql_single);
			while(rs.next()){
				String no = rs.getString(3);
				Object[] o = new Object[10];
				for (int i = 0; i < o.length; i++) {
					o[i] = rs.getObject(i+1);
				}
				List<Object[]> objs = lts_single.get(no);
				if(objs == null){
					objs = new ArrayList<>();
					lts_single.put(no, objs);
				}
				objs.add(o);
			}

			rs.close();
			rs = st.executeQuery(getlinesql_loop);
			while (rs.next()) {
				String no = rs.getString(3);
				Object[] o = new Object[10];
				for (int i = 0; i < o.length; i++) {
					o[i] = rs.getObject(i + 1);
				}
				List<Object[]> objs = lts_loop.get(no);
				if (objs == null) {
					objs = new ArrayList<>();
					lts_loop.put(no, objs);
				}
				objs.add(o);
			}
			rs.close();
			rs = st.executeQuery(getlinesql_dc);
			while (rs.next()) {
				String no = rs.getString(3);
				Object[] o = new Object[10];
				for (int i = 0; i < o.length; i++) {
					o[i] = rs.getObject(i + 1);
				}
				List<Object[]> objs = lts_dc.get(no);
				if (objs == null) {
					objs = new ArrayList<>();
					lts_dc.put(no, objs);
				}
				objs.add(o);
			}
			rs.close();
			rs = st.executeQuery(getlinesql_notower);
			while (rs.next()) {
				String no = rs.getString(3);
				Object[] o = new Object[10];
				for (int i = 0; i < o.length; i++) {
					o[i] = rs.getObject(i + 1);
				}
				List<Object[]> objs = lts_notower.get(no);
				if (objs == null) {
					objs = new ArrayList<>();
					lts_notower.put(no, objs);
				}
				objs.add(o);
			}
			rs.close();
		} finally {
			st.close();
			closeConnection();
		}
		return this;
	}
	
	public void saveToFile() throws  Exception{
		BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream("e:\\aaa\\"+System.currentTimeMillis()+"_1.csv")));
		bw.write("lineId,lineName,lineString");
		bw.newLine();
		for(Map.Entry<String,List<Object[]>> et  : lts_single.entrySet()){
			String lname = et.getKey();
			List<Object[]> ts = et.getValue();
			int len = ts.size();

			if(len > 2){
				String lineid = "-1";
				Object lid = ts.get(0)[0];
				if(null != lid){
					lineid = lid.toString();
				}
				StringBuilder sb = new StringBuilder();
				sb.append("\""+lineid+"\"");
				sb.append(",");
				sb.append("\""+lname+"\"");
				sb.append(",");
				sb.append("\""+"LINESTRING (");
				int i = 0;
				for(Object[] ds : ts){
					if(i == 0)
						sb.append(ds[8]+" "+ds[9]);
					else
						sb.append(", "+ds[8]+" "+ds[9]);
					i++;
				}
				sb.append(")"+"\"");
				bw.write(sb.toString());
				bw.newLine();
			}
		}
		bw.close();
		bw = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream("e:\\aaa\\"+System.currentTimeMillis()+"_2.csv")));
		bw.write("lineId,lineName,loopId,loopName,lineString");
		bw.newLine();
		for(Map.Entry<String,List<Object[]>> et  : lts_loop.entrySet()){
			String lname = et.getKey();
			List<Object[]> ts = et.getValue();
			int len = ts.size();
			if(len > 2){
				String lineid = "-1";
				Object lid = ts.get(0)[0];
				if(null != lid){
					lineid = lid.toString();
				}
				String loopid = "-1";
				Object llid = ts.get(0)[1];
				if(llid != null){
					loopid = llid.toString();
				}
				String loopname = "-1";
				Object llname = ts.get(0)[3];
				if(llid != null){
					loopname = llname.toString();
				}
				StringBuilder sb = new StringBuilder();
				sb.append("\""+lineid+"\"");
				sb.append(",");
				sb.append("\""+lname+"\"");
				sb.append(",");
				sb.append("\""+loopid+"\"");
				sb.append(",");
				sb.append("\""+loopname+"\"");
				sb.append(",");
				sb.append("\""+"LINESTRING (");
				int i = 0;
				for(Object[] ds : ts){
					if(i == 0)
						sb.append(ds[8]+" "+ds[9]);
					else
						sb.append(", "+ds[8]+" "+ds[9]);
					i++;
				}
				sb.append(")"+"\"");
				bw.write(sb.toString());
				bw.newLine();
			}
		}
		bw.close();
	    bw = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream("e:\\aaa\\"+System.currentTimeMillis()+"_3.csv")));
		bw.write("lineId,lineName,lineString");
		bw.newLine();
		for(Map.Entry<String,List<Object[]>> et  : lts_dc.entrySet()){
			String lname = et.getKey();
			List<Object[]> ts = et.getValue();
			int len = ts.size();

			if(len > 2){
				String lineid = "-1";
				Object lid = ts.get(0)[0];
				if(null != lid){
					lineid = lid.toString();
				}
				StringBuilder sb = new StringBuilder();
				sb.append("\""+lineid+"\"");
				sb.append(",");
				sb.append("\""+lname+"\"");
				sb.append(",");
				sb.append("\""+"LINESTRING (");
				int i = 0;
				for(Object[] ds : ts){
					if(i == 0)
						sb.append(ds[8]+" "+ds[9]);
					else
						sb.append(", "+ds[8]+" "+ds[9]);
					i++;
				}
				sb.append(")"+"\"");
				bw.write(sb.toString());
				bw.newLine();
			}
		}
		bw.close();
		bw = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream("e:\\aaa\\"+System.currentTimeMillis()+"_4.csv")));
		bw.write("lineId,lineName,lineString");
		bw.newLine();
		for(Map.Entry<String,List<Object[]>> et  : lts_notower.entrySet()){
			String lname = et.getKey();
			List<Object[]> ts = et.getValue();
			int len = ts.size();

			if(len > 0){
				String lineid = "-1";
				Object lid = ts.get(0)[0];
				if(null != lid){
					lineid = lid.toString();
				}
				StringBuilder sb = new StringBuilder();
				sb.append("\""+lineid+"\"");
				sb.append(",");
				sb.append("\""+lname+"\"");
				sb.append(",");
				sb.append("\""+"LINESTRING (");
				int i = 0;
				for(Object[] ds : ts){
					if(i == 0)
						sb.append(ds[8]+" "+ds[9]);
					else
						sb.append(", "+ds[8]+" "+ds[9]);
					i++;
				}
				sb.append(")"+"\"");
				bw.write(sb.toString());
				bw.newLine();
			}
		}
		bw.close();
	}

	public static void main(String[] args) {
		try {
			new GenLine().query().saveToFile();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
